package br.uniceub.fsw.agenda.model.dao;

import br.uniceub.fsw.agenda.exception.BusinessException;
import br.uniceub.fsw.agenda.exception.DatabaseException;
import br.uniceub.fsw.agenda.exception.UniqueConstraintException;
import br.uniceub.fsw.agenda.model.vo.MenuVO;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Bruno
 */
public class MenuDAO extends GenericDAO {

    public MenuDAO(Connection con) {
        super(con);
    }
    
    /**
     * Devolve lista com objetos menu ordenados por ordem alfabetica
     * @return
     * @throws BusinessException
     * @throws DatabaseException
     */
    public List<MenuVO> findAll() throws BusinessException, DatabaseException {
        //string de consulta
        StringBuilder query = new StringBuilder();
        query.append("SELECT * FROM TB_MENU ORDER BY NO_MENU");
        
        List<MenuVO> listaRegistros = new ArrayList<MenuVO>();
        
        try {
            //objeto con é herdado da super classe
            Statement st = this.con.createStatement();
            ResultSet rs = st.executeQuery(query.toString());
            while (rs.next()) {
                MenuVO menu = new MenuVO();
                
                menu.setIdMenu(rs.getInt("ID_MENU"));
                menu.setNoMenu(rs.getString("NO_MENU"));
                menu.setDsChamada(rs.getString("DS_CHAMADA"));
                
                listaRegistros.add(menu);
            }
            st.close();
            rs.close();
            return listaRegistros;
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new DatabaseException("Erro ao buscar menus", ex);
        }        
    }
    
    /**
     * Buscar objeto Menu pelo seu id
     * @param idMenu código do menu que se deseja buscar
     * @return objeto Menu
     * @throws br.uniceub.fsw.agenda.exception.DatabaseException
     */
    public MenuVO findById(Integer idMenu) throws DatabaseException {
        StringBuilder query = new StringBuilder();
        query.append("SELECT * FROM TB_MENU WHERE ID_MENU = ?");
        
        try {
            PreparedStatement st = con.prepareStatement(query.toString());
            st.setString(1, idMenu.toString());
            
            ResultSet rs = st.executeQuery();

            MenuVO menu = new MenuVO();
            
            while (rs.next()) {
                menu.setIdMenu(rs.getInt("ID_MENU"));
                menu.setNoMenu(rs.getString("NO_MENU"));
                menu.setDsChamada(rs.getString("DS_CHAMADA"));
            }
            
            st.close();
            rs.close();
            
            return menu;
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new DatabaseException("Erro no DAOMenu.findById()", ex);
        }
    }

    /**
     * Buscar lista de menus por parte de seu nome
     * @param noMenu parte do nome dos menus que se deseja buscar
     * @return List de objetos Menu
     * @throws br.uniceub.fsw.agenda.exception.DatabaseException
     */
    public List<MenuVO> findAllByNome(String noMenu) throws DatabaseException {
        StringBuilder query = new StringBuilder();
        query.append("SELECT * FROM TB_MENU WHERE NO_MENU LIKE ? ORDER BY NO_MENU");
        
        try {
            PreparedStatement st = con.prepareStatement(query.toString());
            String filtro = "%" + noMenu + "%";
            st.setString(1, filtro);
            
            List<MenuVO> listaRegistros = new ArrayList<MenuVO>();
            
            ResultSet rs = st.executeQuery();

            while (rs.next()) {
                MenuVO menu = new MenuVO();
                menu.setIdMenu(rs.getInt("ID_MENU"));
                menu.setNoMenu(rs.getString("NO_MENU"));
                menu.setDsChamada(rs.getString("DS_CHAMADA"));
                
                listaRegistros.add(menu);
            }
            st.close();
            rs.close();
            return listaRegistros;
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new DatabaseException("Erro no DAOMenu.findAllByNome()", ex);
        }
    }

    /**
     * Faz o insert de um novo menu e devolve a o id recem gerado
     * @param Menu menu que se deseja inserir
     * @return ID auto-increment gerado para esse registro
     * @throws br.uniceub.fsw.agenda.exception.BusinessException
     * @throws br.uniceub.fsw.agenda.exception.DatabaseException
     */
    public Integer insert(MenuVO menu) throws BusinessException, DatabaseException {        
        StringBuilder query = new StringBuilder();
        query.append("INSERT INTO TB_MENU(NO_MENU, DS_CHAMADA) ");
        query.append("VALUES (?,?)");             
        
        //retornar id gerado
        Integer generatedId = null;
        
        try {           
            PreparedStatement st = con.prepareStatement(query.toString());
            st.setString(1, menu.getNoMenu());
            st.setString(2, menu.getDsChamada());
            
            st.executeUpdate();
            
            //pegar id gerado
            ResultSet rs = st.getGeneratedKeys();
            while (rs.next()) {
                generatedId = new Integer(rs.getString(1));                
            }                    
            
            return generatedId;            
        } catch (SQLException ex) {            
            ex.printStackTrace();
            if (ex.getErrorCode() == 1062) {
                throw new UniqueConstraintException(ex.getMessage(), ex);                
            }
            throw new DatabaseException("Erro no DAOMenu.insert()", ex);            
        }      
    }
    
    /**
     * Faz o update de um menu
     * @param Menu menu que se deseja alterar
     * @throws br.uniceub.fsw.agenda.exception.BusinessException
     * @throws br.uniceub.fsw.agenda.exception.DatabaseException
     */
    public void update(MenuVO menu) throws BusinessException, DatabaseException {        
        StringBuilder query = new StringBuilder();
        query.append("UPDATE TB_MENU SET NO_MENU = ?, DS_CHAMADA = ? ");
        query.append("WHERE ID_MENU = ?");             
        
        try {           
            PreparedStatement st = con.prepareStatement(query.toString());
            st.setString(1, menu.getNoMenu());
            st.setString(2, menu.getDsChamada());
            st.setString(3, menu.getIdMenu().toString());
            
            st.executeUpdate();
        } catch (SQLException ex) {            
            ex.printStackTrace();
            throw new DatabaseException("Erro no DAOMenu.update()", ex);
        }      
    }
    
    /**
     * Faz o delete de um menu
     * @param Menu menu que se deseja excluir
     * @throws br.uniceub.fsw.agenda.exception.BusinessException
     * @throws br.uniceub.fsw.agenda.exception.DatabaseException
     */
    public void delete(MenuVO menu) throws BusinessException, DatabaseException {        
        StringBuilder query = new StringBuilder();
        query.append("DELETE FROM TB_MENU ");
        query.append("WHERE ID_MENU = ?");
        
        try {           
            PreparedStatement st = con.prepareStatement(query.toString());
            st.setString(1, menu.getIdMenu().toString());
            
            st.executeUpdate();
        } catch (SQLException ex) {            
            ex.printStackTrace();
            throw new DatabaseException("Erro no DAOMenu.delete()", ex);            
        }      
    }

}
